<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of UserModel
 *
 * @author Marko
 */
class Rudolph_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    public function rudolph_points($gender, $age, $distance, $stroke, $result_ms) {
        $where = "`gender` = '$gender' AND `distance` = $distance AND `stroke` = '$stroke' AND $age >= `age` AND $result_ms >= `time_ms`";
        $query = $this->db->select('points')
                        ->where($where)
//                ->where('gender', $gender)
//                ->where("$age >=", 'age')
//                ->where('distance', $distance)
//                ->where('stroke', $stroke)
//                ->where("$result_ms >=", 'time_ms')
                        ->from('rudolph')->get();
        return $query->result();
    }
    
    public function fetch_rudolph_admin_table() {
        $query = $this->db->query("SELECT year, CASE age WHEN 109 THEN 'lahtine' ELSE age END as age, count(id) as no FROM rudolph GROUP by year, age ORDER by year, CASE age WHEN 'lahtine' THEN 109 ELSE age END");
        return $query->result();
    }

    public function fetch_rudolph_tabel($pool = false, $stroke = 'free') {
        $pool_query = ($pool == false) ? "" :  "AND c.pool = $pool AND c.id = r.competition_id";
        $query = $this->db->query(
        "SELECT sub_a.id, min(r.result_ms) as ms, r.distance, 
        r.stroke, sub_a.last_name, sub_a.first_name, sub_a.age,
        COALESCE (
            (SELECT `points` FROM `rudolph` WHERE `gender` = sub_a.gender AND `distance` = r.distance 
            AND `stroke` = r.stroke AND competition_id = r.competition_id AND NOT (stroke='medley' AND distance = 100) AND
            `age` >= sub_a.age AND time_ms >= min(r.result_ms) order by age asc, time_ms asc LIMIT 1)
            , (SELECT min(points) FROM `rudolph`)) as rudolph
            FROM results as r, competitions as c,
                (SELECT athletes.id, athletes.last_name, athletes.first_name, athletes.gender, 
                YEAR(CURDATE()) - YEAR(athletes.dob) - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(athletes.dob, '%m%d')) as age 
                FROM athletes, teams
                WHERE teams.full_name LIKE 'Tartu Ujumisklubi' AND athletes.team_id = teams.id ) sub_a
        WHERE distance>=50 AND r.athlete_id = sub_a.id AND NOT r.result_ms = " . MAX_INT . "  
        AND r.stroke = '$stroke' AND NOT (r.stroke='medley'  AND r.distance = 100) " . $pool_query .  "
        GROUP BY r.distance, sub_a.id ORDER by sub_a.last_name, sub_a.first_name, r.distance;");

        return $query->result();
    }

    function insert_rudolph_table_to_db(array $rudolph) {
        $distances = array(50, 100, 200, 400, 800, 1500, 50, 100, 200, 50, 100, 200, 50, 100, 200, 200, 400);
        $gender = '';
        $age = 0;
        $values = array();
        $data['year'] = 2012;
        $data['pool'] = 50;
        foreach ($rudolph as $row) {
            $row = strtolower($row);
            $line = explode(' ', $row);
            if (!is_numeric($line[0])) {
                $row = utf8_encode($row);
                $age_matches = null;
                preg_match_all('/(?<=altersklasse )[0-9]+|(offen)/i', $row, $age_matches);
                if (isset($age_matches[0][0])) {
                    $age = $age_matches[0][0];
                    $age = $age == 'offen' ? 109 : $age;
                }
                $gender_matches = null;
                preg_match_all('/(?<=punkttabelle )(männlich|weiblich)/i', $row, $gender_matches);
                if (isset($gender_matches[0][0])) {
                    switch ($gender_matches[0][0]) {
                        case 'männlich':
                            $gender = 'M';
                            break;
                        case 'weiblich':
                            $gender = 'F';
                            break;
                        default:
                            break;
                    }
                }
                $data['age'] = $age;
                $data['gender'] = $gender;
                continue;
            } else if (count($line) == 19) {
                $data['points'] = $line[0];
                for ($i = 1; $i < count($line) - 1; $i++) {
                    if ($i >= 16) {
                        $stroke = 'medley';
                    } else if ($i >= 13) {
                        $stroke = 'back';
                    } else if ($i >= 10) {
                        $stroke = 'fly';
                    } else if ($i >= 7) {
                        $stroke = 'breast';
                    } else {
                        $stroke = 'free';
                    }
                    $data['distance'] = $distances[$i - 1];
                    $data['stroke'] = $stroke;
                    $data['time_ms'] = $this->Utils->result_to_ms($line[$i]);
                    $values[] = $data;
                }
            }
        }
        $this->db->ignore();
        $this->db->insert_batch('rudolph', $values);
    }

}

?>
